﻿CREATE PROCEDURE [sp_ssiscatalog].[get_executable_stats]
		@exec_id			BIGINT												/*Specify which execution we want to return information for*/
AS
BEGIN
		SET NOCOUNT ON;
		DECLARE	@ganntWidth	INT = 250;
		IF EXISTS (		SELECT	*
						FROM	[$(SSISDB)].[catalog].[executable_statistics] es
						WHERE	es.[execution_id] = @exec_id
				)       
		BEGIN				
				WITH cte AS (
						SELECT	[ResultSet]=N'EXECUTABLE_STATS',es.[execution_path],es.[start_time],[end_time]=ISNULL(es.[end_time],SYSUTCDATETIME()),[execution_duration_ms]=CONVERT(DECIMAL(18,4),es.[execution_duration]),erd.[execution_result_desc]
						,		[parent_execution_path] = SUBSTRING(es.[execution_path],1,LEN(es.[execution_path]) - CHARINDEX('\',REVERSE(es.[execution_path]),1))
						FROM	[$(SSISDB)].[catalog].[executable_statistics] es
						CROSS APPLY [sp_ssiscatalog].[get_execution_result_desc_from_execution_result](es.[execution_result]) erd
						WHERE	es.[execution_id] = @exec_id
				)
				,	limits AS (
						SELECT	OverallExecutionDuration = CONVERT(DECIMAL(18,4),DATEDIFF(MS,l.[OverallStartTime],l.[OverallEndTime])),l.[OverallStartTime],l.[OverallEndTime]
						FROM	(
								SELECT	[OverallStartTime]=MIN([start_time])
								,		[OverallEndTime]=MAX([end_time])
								FROM	[cte]
								)l
				)
				SELECT	c.[ResultSet]
				,		c.[execution_path]
				,		c.[start_time]
				,		c.[end_time]
				,		c.[execution_duration_ms]
				,		c.[execution_result_desc]
				,		[Gannt]=REPLICATE(' ' ,[RelativeStartTime] / [OverallExecutionDuration] * @ganntWidth) + (REPLICATE('-',CEILING([execution_duration_ms] / [OverallExecutionDuration] * @ganntWidth)))
				FROM	(
						SELECT	c.[ResultSet]
						,		c.[execution_path]
						,		c.[start_time]
						,		c.[end_time]
						,		c.[execution_duration_ms]
						,		c.[execution_result_desc]
						,		[OverallStartTime]
						,		[OverallEndTime]
						,		[RelativeStartTime] = CONVERT(DECIMAL(18,4),DATEDIFF(MS,l.[OverallStartTime],c.[start_time]))
						,		l.OverallExecutionDuration
						,		c.[parent_execution_path]
						FROM	cte c
						CROSS JOIN limits l
						)c
				ORDER	BY c.[parent_execution_path] ASC, c.[start_time] ASC;
				RAISERROR('%d executable stats returned',1,1,@@ROWCOUNT);
		END
		ELSE
		BEGIN      
				DECLARE @exec_id_as_string NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX),@exec_id);
				RAISERROR('No executable stats to be returned for execution_id=%s',1,1,@exec_id_as_string);    
		END
END